﻿print 'Rollback v1.24'
go

print 'ALTER PROCEDURE dbo.RepAnalizKpoPol'
go

ALTER PROCEDURE dbo.RepAnalizKpoPol
(
	@D_S datetime,
	@D_PO datetime, 
	@K_SPEC int,
	@K_RAB int,
	@K_ANALIZ_KPO int,
	@K_RAZDEL int
)
AS
	-- Анализ КПО - Распределение по полу
BEGIN
SET STATISTICS XML OFF
INSERT INTO bo_analiz_kpo_
                      (C_NE_PROVED_DEN, C_NE_PROVED_HOLIDAY, C_NE_PROVED_NOCH, C_PROVED_DEN, C_PROVED_NOCH, C_PROVED_HOLIDAY, K_RAZDEL, 
                      K_ANALIZ_KPO, N_NAME)
SELECT     SUM(RepAnalizKpo__1.NeProvedDen) AS NeProvedDen, SUM(RepAnalizKpo__1.NeProvedHoliday) AS NeProvedHoliday, 
                      SUM(RepAnalizKpo__1.NeProvedNoch) AS NeProvedNoch, SUM(RepAnalizKpo__1.ProvedDen) AS ProvedDen, SUM(RepAnalizKpo__1.ProvedNoch) 
                      AS ProvedNoch, SUM(RepAnalizKpo__1.ProvedHoliday) AS ProvedHoliday, @K_RAZDEL AS K_RAZDEL, @K_ANALIZ_KPO AS K_ANALIZ_KPO, 
                      CASE WHEN F_KLIENT = 1 THEN 'Мужской' else 'Женский' END AS N_NAME
FROM         dbo.RepAnalizKpo_(@D_S, @D_PO, @K_SPEC, @K_RAB) AS RepAnalizKpo__1 INNER JOIN
                      b_kpo ON RepAnalizKpo__1.K_KPO = b_kpo.K_KPO LEFT OUTER JOIN
                      s_obr ON b_kpo.K_OBR = s_obr.K_OBR
GROUP BY CASE WHEN F_KLIENT = 1 THEN 'Мужской' else 'Женский' END 
ORDER BY CASE WHEN F_KLIENT = 1 THEN 'Мужской' else 'Женский' END  desc
	RETURN
END

go
print 'ALTER PROCEDURE dbo.RepAnalizKpoVozrastDeti'
go

ALTER PROCEDURE dbo.RepAnalizKpoVozrastDeti
(
	@D_S datetime,
	@D_PO datetime, 
	@K_SPEC int,
	@K_RAB int,
	@K_ANALIZ_KPO int,
	@K_RAZDEL int
)
AS
	-- Анализ КПО - Распределение по возрасту детей
BEGIN
SET STATISTICS XML OFF
INSERT INTO bo_analiz_kpo_
                      (C_NE_PROVED_DEN, C_NE_PROVED_HOLIDAY, C_NE_PROVED_NOCH, C_PROVED_DEN, C_PROVED_NOCH, C_PROVED_HOLIDAY, K_RAZDEL, 
                      K_ANALIZ_KPO, N_NAME)
SELECT     SUM(RepAnalizKpo__1.NeProvedDen) AS NeProvedDen, SUM(RepAnalizKpo__1.NeProvedHoliday) AS NeProvedHoliday, 
                      SUM(RepAnalizKpo__1.NeProvedNoch) AS NeProvedNoch, SUM(RepAnalizKpo__1.ProvedDen) AS ProvedDen, SUM(RepAnalizKpo__1.ProvedNoch) 
                      AS ProvedNoch, SUM(RepAnalizKpo__1.ProvedHoliday) AS ProvedHoliday, @K_RAZDEL AS K_RAZDEL, @K_ANALIZ_KPO AS K_ANALIZ_KPO, 
                      s_period_let_deti.N_PERIOD
FROM         b_sostav_semyi_kpo INNER JOIN
                      b_kpo INNER JOIN
                      dbo.RepAnalizKpo_(@D_S, @D_PO, @K_SPEC, @K_RAB) AS RepAnalizKpo__1 ON b_kpo.K_KPO = RepAnalizKpo__1.K_KPO INNER JOIN
                      s_period_let_deti ON YEAR(GETDATE()) - b_kpo.GOD_R <= s_period_let_deti.C_END AND YEAR(GETDATE()) - b_kpo.GOD_R >= s_period_let_deti.C_START ON 
                      b_sostav_semyi_kpo.K_KPO = b_kpo.K_KPO
WHERE     (b_sostav_semyi_kpo.K_RODSTV_OTN BETWEEN 8 AND 13) OR
                      (b_sostav_semyi_kpo.K_RODSTV_OTN BETWEEN 22 AND 23)
GROUP BY s_period_let_deti.N_PERIOD
ORDER BY s_period_let_deti.N_PERIOD
	RETURN
END


go
print 'delete from s_period_let_deti'
go
delete from s_period_let_deti where K_PERIOD_LET = 7 


go
print 'ALTER PROCEDURE RepAnalizKpoVozrast'
go

ALTER PROCEDURE dbo.RepAnalizKpoVozrast
(
	@D_S datetime,
	@D_PO datetime, 
	@K_SPEC int,
	@K_RAB int,
	@K_ANALIZ_KPO int,
	@K_RAZDEL int
)
AS
	-- Анализ КПО - Распределение по возрасту
BEGIN
SET STATISTICS XML OFF
INSERT INTO bo_analiz_kpo_
                      (C_NE_PROVED_DEN, C_NE_PROVED_HOLIDAY, C_NE_PROVED_NOCH, C_PROVED_DEN, C_PROVED_NOCH, C_PROVED_HOLIDAY, K_RAZDEL, 
                      K_ANALIZ_KPO, N_NAME)
SELECT     SUM(RepAnalizKpo__1.NeProvedDen) AS NeProvedDen, SUM(RepAnalizKpo__1.NeProvedHoliday) AS NeProvedHoliday, 
                      SUM(RepAnalizKpo__1.NeProvedNoch) AS NeProvedNoch, SUM(RepAnalizKpo__1.ProvedDen) AS ProvedDen, SUM(RepAnalizKpo__1.ProvedNoch) 
                      AS ProvedNoch, SUM(RepAnalizKpo__1.ProvedHoliday) AS ProvedHoliday, @K_RAZDEL AS K_RAZDEL, @K_ANALIZ_KPO AS K_ANALIZ_KPO, 
                      s_period_let.N_PERIOD
FROM         b_kpo INNER JOIN
                      dbo.RepAnalizKpo_(@D_S, @D_PO, @K_SPEC, @K_RAB) AS RepAnalizKpo__1 ON b_kpo.K_KPO = RepAnalizKpo__1.K_KPO INNER JOIN
                      s_period_let ON YEAR(GETDATE()) - b_kpo.GOD_R <= s_period_let.C_END AND YEAR(GETDATE()) - b_kpo.GOD_R >= s_period_let.C_START
GROUP BY s_period_let.N_PERIOD
ORDER BY s_period_let.N_PERIOD
	RETURN
END